package com.sushe.dao;

import com.sushe.entity.DormBuilding;

import java.sql.*;

public class RoomDao {
    private static final String url="jdbc:mysql://localhost:3306/sushe";
    private static final String username="root";
    private static final String password="123456";

    //添加房间
    public  void addRoom(String roomNumber,Integer buildingId) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //  获取连接
        String sql="insert into room (room_number,building_id) values(?,?)";
        Connection connection= DriverManager.getConnection(url,username,password);
        //  创建statement
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setString(1,roomNumber);
        stmt.setInt(2,buildingId);
        int i=stmt.executeUpdate();
        System.out.println("SQL语句执行完毕影响的记录数为"+i);
        stmt.close();
        connection.close();
    }

    //添加房间时检查对应宿舍楼是否存在
    public  boolean selectByBuildingId(Integer buildingId) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql="select * from dorm_building where id=?";
        Connection connection= DriverManager.getConnection(url, username, password);
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setInt(1,buildingId);
        ResultSet rs=stmt.executeQuery();
        while(rs.next()) {
            Integer Id = rs.getInt("id");
            String name = rs.getString("name");
            if(name!=null)
                return true;
        }
        return false;
    }
    //获取roomId为添加Bed做准备
    public  Integer getRoomId(String roomNumber) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql="select * from room where room_number=?";
        Connection connection= DriverManager.getConnection(url, username, password);
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setString(1,roomNumber);
        ResultSet rs=stmt.executeQuery();
        while(rs.next()) {
            Integer Id = rs.getInt("id");
            return Id;
        }
        return null;
    }

    public  Integer selectByRoomNumberAndBuildingId(String roomNumber,Integer buildingId) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql="select id from room where room_number=? and building_id=?";
        Connection connection= DriverManager.getConnection(url, username, password);
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setString(1,roomNumber);
        stmt.setInt(2,buildingId);
        ResultSet rs=stmt.executeQuery();
        while(rs.next()) {
            Integer Id = rs.getInt("id");
            if(Id!=null)
                return Id;
        }
        return null;
    }

    public  String  getRoomNumberByRoomId(Integer roomId) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql="select room_number from room where id=?";
        Connection connection= DriverManager.getConnection(url, username, password);
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setInt(1,roomId);

        ResultSet rs=stmt.executeQuery();
        while(rs.next()) {
            return rs.getString("room_number");
        }
        return null;
    }

    public  void delete(Integer roomId) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //  获取连接
        String sql="delete from room where id=?";
        Connection connection= DriverManager.getConnection(url,username,password);
        //  创建statement
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setInt(1,roomId);

        int i=stmt.executeUpdate();
        System.out.println("SQL语句执行完毕影响的记录数为"+i);
        stmt.close();
        connection.close();
    }
}
